package com.swordget.dao.impl;

import com.alibaba.druid.pool.DruidDataSource;
import com.swordget.commons.JdbcUtil;
import com.swordget.dao.TableManagerDao;
import com.swordget.entity.ColumnDesign;
import com.swordget.entity.TableDesign;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import static com.swordget.commons.ColumnType.NUMBER;
import static com.swordget.commons.ColumnType.VARCHAR2;
//import static com.swordget.commons.SimpleFinalUtil.TMP_SUFFIX;

/**
 * on 2020/4/5 17:46
 * 表结构管理 sqlserver 实现
 * @author David
 */
public class TableManagerSqlServerDao extends TableManagerOracleDao implements TableManagerDao {

    @Autowired
    private JdbcUtil jdbcUtil;

    @Autowired
    private DruidDataSource dataSource;

    /**
     * TableManagerSqlServerDao log
     */
    private final static Logger LOG = LoggerFactory.getLogger(TableManagerSqlServerDao.class);

    /**
     * @param tableName 表名
     * @param userName 用户名
     * @return
     */
    @Override
    public boolean dataTableExists(String tableName, String userName) {
        String userExistsSQL = String.format("select count(*) from sysObjects where Id=OBJECT_ID(N'%s')", tableName);
        int result = jdbcUtil.executeCount(userExistsSQL);
        return result > 0;
    }

    /**
     * @param username 用户名
     * @return
     */
    @Override
    public String getTableSpace(String username) {
        return null;
    }

    /**
     * @param tableDesign 表结构
     * @param columnDesigns 字段结构
     * @param serial 主键结构
     * @param tableSpace 表空间
     * @param username 用户名
     * @param loginId 操作用户
     * @return
     */
    @Override
    public boolean createTable(TableDesign tableDesign, List<ColumnDesign> columnDesigns, ColumnDesign serial, String tableSpace, String username, String loginId) {
        StringBuilder tableBuilder = new StringBuilder();
        List<String> commentList = new ArrayList<>();
        String tableName = tableDesign.getTableEN();
        tableBuilder.append(String.format("CREATE TABLE %s(", tableDesign.getTableEN()));
        for (int i = 0; i < columnDesigns.size(); i++) {
            ColumnDesign columnDesign = columnDesigns.get(i);
            String name = columnDesign.getName().toUpperCase();
            String type = columnDesign.getType();
            String comment = columnDesign.getComment();
            //判断类型添加字段长度
            if (VARCHAR2.equals(type)) {
                tableBuilder.append(String.format("%s VARCHAR(%s) ", name, columnDesign.getWidth()));
            } else if (NUMBER.equals(type)) {
                tableBuilder.append(String.format("%s DECIMAL(%s,%s) ", name, columnDesign.getWidth(), columnDesign.getPoint()));
            }
            //是否允许为空,添加了索引的字段默认不能为空
            if ("Y".equals(columnDesign.getNotNull())) {
                tableBuilder.append("NOT NULL ");
            }
            if (serial.getName().equals(name)) {
                tableBuilder.append("UNIQUE ");
            }
            //最后一个字段不加逗号
            if (i != columnDesigns.size() - 1) {
                tableBuilder.append(",");
            }
            //添加注释
            commentList.add(String.format("EXEC sp_addextendedproperty N'MS_Description', " +
                            "N'%s', N'user', N'dbo', N'table', N'%s', N'column', N'%s'",
                    comment, tableName, name));
        }
        tableBuilder.append(")");
        try {
            //执行创建表语句
            boolean isCreate = jdbcUtil.execute(tableBuilder.toString());
            return isCreate;
            //TODO: jdbc执行注释太恶心了,不知道如何执行,后面研究
            //jdbcUtil.executeBatch(commentList);
           /* if (isCreate) {
                //执行注释语句

            }*/
            //return true;
        } catch (Exception e) {
            LOG.error("程序执行过程中出现异常", e);
        }
        return false;
    }

    @Override
    public boolean dropTable(String tableName) {
        String dropSQL = String.format("IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE " +
                "TABLE_NAME = '%s') DROP TABLE %s", tableName, tableName);
        //删除临时表
      /*  String tmpName = tableName + TMP_SUFFIX;
        String dropTmpSQL = String.format("IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE " +
                "TABLE_NAME = '%s') DROP TABLE %s", tmpName, tmpName);*/
        return jdbcUtil.executeBatch(dropSQL);
    }

    @Override
    public List<TableDesign> findTableDesignByPage(int rows, int page, String tableEn, String tableCn) {
        String command = String.format("SELECT TOP %d * FROM TABLEDESIGN WHERE (SERIALNO NOT IN " +
                "(SELECT TOP %d SERIALNO FROM TABLEDESIGN ORDER BY CREATEDATE DESC)) ORDER BY CREATEDATE DESC" , rows, (page - 1) * rows);

        /*String command = String.format("SELECT * FROM TABLEDESIGN ORDER BY CREATEDATE DESC " +
                "OFFSET %d ROWS FETCH NEXT %d ROWS ONLY", (page - 1) * rows, rows);*/
        try(Connection conn = dataSource.getConnection();
            Statement statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery(command)) {
            List<TableDesign> data = new ArrayList<>();
            while (resultSet.next()) {
                data.add(TableDesign.builder()
                        .tableId(resultSet.getString("SERIALNO"))
                        .tableEN(resultSet.getString("TABLENAME"))
                        .tableCN(resultSet.getString("TABLENCOM"))
                        .loginId(resultSet.getString("CREATEUSER"))
                        .build());

            }
            return data;
        } catch (SQLException e) {
            LOG.error("SQL异常", e);
        }
        return null;

    }
}
